Method and system for converting a schema-based hierarchical data structure into a flat data structure

ABSTRACT

A method and system are provided for converting a hierarchical data structure into a flat data structure based on a schema. The format of the hierarchical data structure may be XML. A hierarchical data structure conforming to a schema is loaded into an application program. The data structure includes elements and attributes linked together in a parent-child relationship. The schema defines the hierarchical relationships between the elements and attributes in the hierarchical data structure. After the hierarchical data structure conforming to the schema has been loaded, a plurality of layout rules is applied to the hierarchical data based on the schema to create a flat data structure. The layout rules determine how the hierarchical data will be inserted in the flat data structure. The layout rules may be applied by identifying properties in the schema which identify occurrence requirements for elements defined in the schema. After the layout rules have been applied to the hierarchical data, the hierarchical data is rendered into a spreadsheet by inserting the data in rows and columns.

TECHNICAL FIELD

The present invention relates generally to the conversion of highlyhierarchical data structures into non-hierarchical (or flat) datastructures. More particularly, the present invention relates toreceiving input data associated with a schema in a standard format andconverting it into a flat table data structure based on the schema.

BACKGROUND OF THE INVENTION

Historically, computer systems and databases have contained data inincompatible formats. As the organizations utilizing these systems anddatabases became more automated and computerized, the need to share dataover computer networks, such as the Internet, became more and moreprevalent. Due to this need, standard formats for the sharing of datawere developed.

One such standard format developed is Extensible Markup Language(“XML”). XML is a very hierarchical data format, which includes amultitude of data structures having parent-child relationships. In theXML data format, the children of each parent may have further children,which in turn may have still further children, and so on. Due to theadvent of this standard data format, many computer users have convertedthe aforementioned databases to the standard XML data format. The XMLformat also allows computer users to tailor their data to specificdocuments using schemas. Schemas provide mechanisms to define anddescribe the structure, content, and to some extent semantics of XMLdocuments.

Along with the advent of the personal computer on the modern businesslandscape came many useful application programs including thoseutilizing electronic spreadsheets. Electronic spreadsheets typicallyoperate on data contained in a matrix of cells comprising columns androws. Generally, this data format is contained in a single table and isa very flat data structure in that the data is not maintained inhierarchical data structure utilized by XML.

With the ease of availability of the personal computer, many users havebecome proficient in their use and more specifically, in usingelectronic spreadsheet application programs. While electronicspreadsheets offer may advantages over prior conventional means, theyalso utilize a flat data structure. With huge amounts of data stored inhierarchical formats such as XML, the electronic spreadsheet with itsflat data structure had no means to access this wealth of data, thus ameans for accessing it with a spreadsheet has become desirable. Whilethere are programs currently capable of converting XML formatted datainto a spreadsheet, current programs fail to take into account the datarelationships defined in schemas associated with hierarchical formatteddocuments. As a result, the display of hierarchical data contained in adocument in a spreadsheet using current programs may be vastly differentthan the display intended by the author of the document.

It is with respect to these considerations and others that the presentinvention has been made.

SUMMARY OF THE INVENTION

In accordance with the present invention, the above and other problemsare solved by a method and system for converting a hierarchical datastructure into a flat data structure, based on a schema associated withthe hierarchical data structure, suitable for use in electronicspreadsheet tables.

In accordance with one aspect of the present invention, a method andsystem are provided for converting a hierarchical data structure into aflat data structure based on a schema. The format of the hierarchicaldata structure may be XML.

First, the hierarchical data structure conforming to a schema is loadedinto an application program. The data structure includes elements andattributes linked together in a parent-child relationship. The schemadefines the hierarchical relationships between the elements andattributes in the hierarchical data structure.

Next, a plurality of layout rules is applied to the hierarchical databased on the schema. The layout rules determine how the hierarchicaldata will be inserted in the resulting flat data structure. The layoutrules may be applied by identifying properties in the schema whichidentify occurrence requirements for elements defined in the schema.These properties may include a specifying the order in which elementsoccur in the hierarchical data and the number of times each element mayoccur. Once the properties have been identified, the layout rules areapplied to the hierarchical data based on the identified properties. Thelayout rules may also be applied based on the type of elements definedin the schema. These element types may include attributes, ancestorelements, and sibling elements.

Finally, the layout rules are applied to the hierarchical data to createa flat data structure and the flat data structure is then rendered intothe spreadsheet by inserting the data in rows and columns.

Aspects of the invention may be implemented as a computer process, acomputing system, or as an article of manufacture such as a computerprogram product or computer-readable medium. The computer programproduct may be a computer storage media readable by a computer systemand encoding a computer program of instructions for executing a computerprocess. The computer program product may also be a propagated signal ona carrier readable by a computing system and encoding a computer programof instructions for executing a computer process.

These and various other features as well as advantages, whichcharacterize the present invention, will be apparent from a reading ofthe following detailed description and a review of the associateddrawings.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of a computer system that provides theillustrative operating environment for the present invention.

FIG. 2 illustrates an operational flow for converting XML data into aflat data structure based on input XML schema data according to anembodiment of the present invention.

FIG. 3 illustrates an operational flow for applying layout rules to XMLdata according to various embodiments of the present invention.

FIG. 4 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 5 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 6 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 7 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 8 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 9 is a screenshot illustrating a flat data structure resulting fromconverted XML data according to an illustrative embodiment of thepresent invention.

FIG. 10 is a screenshot illustrating a flat data structure resultingfrom converted XML data according to an illustrative embodiment of thepresent invention.

FIG. 11 is a screenshot illustrating a flat data structure resultingfrom converted XML data according to an illustrative embodiment of thepresent invention.

DETAILED DESCRIPTION OF THE INVENTION

The present invention is directed to a method and system for flatteninginput XML data based on a schema associated with the data. In oneembodiment, the present invention is incorporated into the “OFFICE”suite of application programs that is marketed by Microsoft Corporationof Redmond, Wash. Briefly described, the invention provides forreceiving data formatted in a hierarchical data structure. An example ofsuch a format could include XML, however those skilled in the art willappreciate that many other database structures are highly hierarchical.

In the following detailed description, references are made to theaccompanying drawings that form a part hereof, and in which are shown byway of illustration specific embodiments or examples. Referring now tothe drawings, in which like numerals represent like elements through theseveral figures, aspects of the present invention and the illustrativeoperating environment will be described.

Referring now to FIG. 1, an illustrative operating environment forimplementing an illustrative embodiment of the present invention isshown. Within the illustrative operating environment, the presentinvention may operate to facilitate the flattening of an XML datastructure conforming to a schema into a flat data structure based on theschema. However, those skilled in the art should appreciate that theinvention may be practiced in any type of computer operating environmentsuch as hand-held devices, multiprocessor systems, microprocessor-basedor programmable consumer electronics, minicomputers, mainframecomputers, and the like. The invention may also be practiced indistributed computing environments where tasks are performed by remoteprocessing devices.

The illustrative embodiment of the present invention will be describedin the general context of a XML flattening program module 36 whichreceives an input XML schema data 38 and data from input XML data 37 andconverts the data into a flat data structure 39 based on the schemadata. The flat data structure 39 may be inserted by a spreadsheet in theapplication program. Those skilled in the art will recognize that theinvention may be implemented in combination with various other programmodules (not shown). Generally, program modules include routines,programs, components, data structures, etc., that perform particulartasks or implement particular abstract data types. Moreover, thoseskilled in the art will appreciate that the invention may be practicedwith computer system configurations other than the one shown, that arelinked through a communications network. In a distributed computingenvironment, program modules may be located in both local and remotememory storage devices.

An illustrative operating environment 10 for implementing the inventionincludes a conventional personal computer system 20, including aprocessing unit 21, a system memory 22, and a system bus 23 that couplesthe system memory to the processing unit 21. The system memory 22includes read only memory (ROM) 24 and random access memory (RAM) 25. Abasic input/output system 26 (BIOS), containing the basic routines thathelp to transfer information between elements within the personalcomputer 20, such as during start-up, is stored in ROM 24. The personalcomputer 20 further includes a hard disk drive 27, a magnetic disk drive28, e.g., to read from or write to a removable disk 29, and an opticaldisk drive 30, e.g., for reading a CD-ROM disk 31 or to read from orwrite to other optical media. The hard disk drive 27, magnetic diskdrive 28, and optical disk drive 30 are connected to the system bus 23by a hard disk drive interface 32, a magnetic disk drive interface 33,and an optical drive interface 34, respectively. The drives and theirassociated computer-readable media provide nonvolatile storage for thepersonal computer 20. Although the description of computer-readablemedia above refers to a hard disk, a removable magnetic disk and aCD-ROM disk, it should be appreciated by those skilled in the art thatother types of media which are readable by a computer, such as magneticcassettes, flash memory cards, digital video disks, Bernoullicartridges, and the like, may also be used in the exemplary operatingenvironment.

A number of program modules may be stored in the drives and RAM 25,including an operating system 35, an XML flattening program module 36,input XML data 37, input XML schema data 38, a flat data structure 39,and other program modules (not shown).

A user may enter commands and information into the personal computersystem 20 through a keyboard 40 and pointing device, such as a mouse 42.Other input devices (not shown) may include a microphone, joystick, gamepad, satellite dish, scanner, or the like. These and other input devicesare often connected to the processing unit 21 through a serial portinterface 46 that is coupled to the system bus, but may be connected byother interfaces, such as a game port or a universal serial bus (USB). Amonitor 47 or other type of display device is also connected to thesystem bus 23 via an interface, such as a video adapter 48. In additionto the monitor, personal computers typically include other peripheraloutput devices (not shown), such as speakers or printers.

The personal computer system 20 may operate in a networked environmentusing logical connections to one or more remote computers, such as aremote computer 49. The remote computer 49 may be a server, a router, apeer device or other common network node, and typically includes many orall of the elements described relative to the personal computer 20,although only a memory storage device 50 has been illustrated in FIG. 1.The logical connections depicted in FIG. 1 include a local area network(LAN) 51 and a wide area network (WAN) 52. Such networking environmentsare commonplace in offices, enterprise-wide computer networks, intranetsand the Internet.

When used in a LAN networking environment, the personal computer system20 is connected to the LAN 51 through a network interface 53. When usedin a WAN networking environment, the personal computer 20 typicallyincludes a modem 54 or other means for establishing communications overthe WAN 52, such as the Internet. The modem 54, which may be internal orexternal, is connected to the system bus 23 via the serial portinterface 46. In a networked environment, program modules depictedrelative to the personal computer 20, or portions thereof, may be storedin the remote memory storage device. It will be appreciated that thenetwork connections shown are illustrative and other means ofestablishing a communications link between the computers may be used. Itwill be further be appreciated that the invention could equivalently beimplemented on host or server computer systems other than personalcomputer systems, and could equivalently be transmitted to the hostcomputer system by means other than a CD-ROM, for example, by way of thenetwork interface 53.

The present invention may be implemented within a dynamic link library(DLL). Once example of such a DLL is the MSO.DLL provided in the“OFFICE” suite of application programs marketed by the MicrosoftCorporation of Redmond Wash. The DLL contains a library of executablefunctions that may be accessed by an application program by creatingeither a static or dynamic link to the DLL. A static link remainsconstant during program execution while a dynamic link is created by theprogram as needed.

FIG. 2 shows illustrative logical operations performed by the XMLflattening program module 36 for converting the input XML data 37 intothe flat data structure 39 based on the input XML schema data 38 in thepersonal computer system 20 described above. The logical operations ofthe various embodiments of the present invention are implemented (1) asa sequence of computer implemented acts or program modules running on acomputing system and/or (2) as interconnected machine logic circuits orcircuit modules within the computing system. The implementation is amatter of choice dependent on the performance requirements of thecomputing system implementing the invention. Accordingly, the logicaloperations making up the embodiments of the present invention describedherein are referred to variously as operations, structural devices, actsor modules. It will be recognized by one skilled in the art that theseoperations, structural devices, acts and modules may be implemented insoftware, in firmware, in special purpose digital logic, and anycombination thereof without deviating from the spirit and scope of thepresent invention as recited within the claims attached hereto.

The logical operations begin at load operation 210 where the input XMLdata 37 containing hierarchical data is loaded into a memory such as theRAM 25 for use by an application program, such as a spreadsheetapplication program. The input XML data 37 may include a schema embeddedin the data which defines the parent-child relationships between XMLelements and attributes appearing in the data. Optionally, the input XMLdata 37 may include a pointer to the input XML schema data 38 which maybe stored on the personal computer system 20 or on a remote computer(such as remote computer 49) accessible by the personal computer system20 over the network interface 53. It will be appreciated that the schemain the input XML schema data 38 is “mapped” to the flat data structure39 in the application program so that the rows and columns of thespreadsheet correspond to the defined hierarchical relationships. Theschema may be mapped by a user (i.e., by dragging and dropping schemaelements into the spreadsheet) or alternatively, the mapping may beperformed automatically by the spreadsheet application program. The“mapped” schema is associated with the data in the input XML data 37once it is loaded into the application program.

Next, the operational flow continues to application operation 215 wherethe XML flattening program module 36 determines how the loaded XML datais to be inserted based on constructs within the schema which has beenmapped to the spreadsheet. The XML flattening program module 36 makesits determination by applying a series of “layout rules” to the data.The layout rules are based on known schema constructs and govern how thedata is to be inserted or “flattened.” It will also be appreciated thatthe XML flattening program module 36 may flatten the entire input XMLdata 37 or a fragment of the data. The XML flattening program module 36flattens fragments from the beginning of the fragment, considering theclosest common parent of all the nodes of the fragment. After applyingthe layout rules, the XML flattening program module 36 creates the flatdata structure. The steps comprising application operation 215 are shownin FIG. 3 and will be described in greater detail below. Finally, theoperational flow continues to rendering operation 220 where the XMLflattening program module 36 renders the “flattened” XML data in thespreadsheet. Illustrative screenshots showing how the data is renderedin the spreadsheet based on the layout rules is shown in FIGS. 4–11 andwill be described in greater detail below.

FIG. 3 illustrates an operational flow for depicting the steps ofapplication operation 215 from FIG. 2 in which the layout rules areapplied to the loaded XML data by the XML flattening program module 36,based on the schema mapped to the spreadsheet. The operational flowbegins at identification operation 310 where the XML flattening programmodule 36 identifies properties and element types defined in the schema.The properties and element types and their associated layout rules aresummarized below:

Properties

-   -   All: This property specifies that elements may occur in any        order, but may only occur once. If the schema contains an All        property, the XML flattening program module 36 will display the        elements within this property on the same row in the        spreadsheet.    -   Sequence: This property specifies that elements must occur in        the specified order and may occur zero to more than once. The        sequence itself may also occur any number of times. If the        schema contains a Sequence property, the XML flattening program        module 36 will display the elements within this property on the        same row in the spreadsheet unless the schema defines an element        as occurring more than once.    -   Choice: This property specifies that elements do not share a        relationship with each other and must be chosen (only one        occurrence per choice). The chosen element, or group may occur        zero to more than once. The choice itself may also occur more        than once. If the schema contains a Choice property, the XML        flattening program module 36 will display the elements within        this compositor on separate rows in the spreadsheet.

Element Types

-   -   Attribute: Attributes are equivalent to child elements in a        schema. If the schema contains an attribute, the XML flattening        program module 36 will display the attributes on the same row as        its parent element in the spreadsheet. The XML flattening        program module 36 will also “fill down” attributes with other        child elements defined in the schema.    -   Ancestor: Elements having descendants (such as child) are        ancestor elements. The XML flattening program module 36 will        “fill down” ancestor elements with their descendants.    -   Sibling: Two or more elements having a common parent are sibling        elements. The XML flattening program module 36 will “fill down”        sibling elements with other sibling elements unless one or more        sibling elements occurs more than once.        As used in this description and the accompanying claims, the        term “fill down” is defined as the act of repeating a data value        in a table. Thus, even though the data may appear only once in        the XML data, it will be displayed more than once in the table.        It should be understood that the All, Sequence, and Choice        properties are also known as compositors. As is known to those        skilled in the art, compositors are XML schema constructs which        identify the occurrence requirements or expectations of an        element's immediate descendants. Members within the same        compositor are considered siblings. At step 315, the XML        flattening program module 36 reviews the schema to determine if        it contains an All compositor. If the schema contains an All        compositor, the XML flattening program module 36 will insert the        elements within this compositor on the same row in the resulting        flat data structure at step 320. If the schema does not contain        an All compositor, the XML flattening program module 36        determines if the schema contains a Sequence compositor at step        325.

An example of an input XML schema data 38 defining elements within anAll compositor and a conforming input XML data 37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:all>       <xsd:element name=“B”type=“xsd:string” />       <xsd:element name=“C” type=“xsd:string” />      <xsd:element name=“D” type=“xsd:string” />       <xsd:elementname=“E” type=“xsd:string” />      </xsd:all>     </xsd:complexType>   </xsd:element>   </xsd:sequence>  </xsd:complexType> </xsd:element>Data <root>  <A>     <B>b1</B>     <C>c1</C>     <D>d1</D>     <E>e1</E> </A>  <A>     <C>c2</C>     <B>b2</B>     <E>e2</E>     <D>d2</D>  </A></root>The input XML schema data 38 illustrated in the above example defines abase element “A” of the XML data structure. Elements “B,” “C,” “D,” and“E” are elements which must be contained within the element “A” and aredefined within the All compositor. The “b1,” “c1,” “d1,” and “e1” in theinput XML data 37 for the schema represent the data contained within theelements “B,” “C,” “D,” and “E.” The “</B>, </C>, </D>, and</E>following the aforementioned data signifies the end of each element.Similarly, the input XML data 37 also indicates “C,” “B,” “E” and “D”elements containing data “c2,” “b2,” “e2,” and “d2” respectively, alsocontained within the element “A.”

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 in the above example is shown in FIG. 4. As shown online 405 in FIG. 4, the column headings represent the elements “B,” “C,”“D,” and “E” defined within the All compositor in the input XML schemadata 38. On line 410, the “b1,” “c1,” “d1,” and “e1” data is displayedon the same row. Similarly, on line 415, the “b2,” “c2,” “d2,” and “e2”data is also displayed in the same row. As briefly described above theAll compositor specifies that elements may be displayed in any order.Thus, it will be appreciated that the order in which the elementsdisplayed may be different from that shown in FIG. 4.

Returning now to FIG. 3, the XML flattening program module 36 reviewsthe schema to determine if it contains a Sequence compositor at step325. If the schema contains a Sequence compositor, the XML flatteningprogram module 36 will insert the elements within this compositor on thesame row at step 330 as shown in FIG. 4 above for the All compositor,unless the schema defines an element as occurring more than once. If theschema does not contain a Sequence compositor, the XML flatteningprogram module 36 determines if the schema contains a Choice compositorat step 335.

An example of an input XML schema data 38 defining elements within aSequence compositor and conforming input XML data 37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:sequence maxOccurs=“unbounded”>      <xsd:element name=“B” type=“xsd:string” />       <xsd:elementname=“C” type=“xsd:string” />       <xsd:element name=“D”type=“xsd:string” />       <xsd:element name=“E” type=“xsd:string” />     </xsd:sequence>     </xsd:complexType>    </xsd:element>  </xsd:sequence>  </xsd:complexType> </xsd:element> Data <root>  <A>    <B>b1</B>     <C>c1</C>     <D>d1</D>   <E>e1</E>     <B>b2</B>    <C>c2</C>     <D>d2</D>     <E>e2</E>  </A> </root>The input XML schema data 38 illustrated in the above example defineselements “B,” “C,” “D,” and “E” as unbounded elements (i.e.,maxOccurs=“unbounded”) within the Sequence compositor indicating thatthe data contained within these elements must occur in the specifiedorder and that each element may occur more than once.

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the unbounded sequence in the above example isshown in FIG. 5. As shown on line 505 in FIG. 5, the column headingsrepresent the elements “B,” “C,” “D,” and “E” defined within theSequence compositor in the input XML schema data 38. On lines 510–525,the “b1,” “c1,” “d1,” and “e1” data are inserted on separate rows.Similarly, on lines 530–545, the “b2,” “c2,” “d2,” and “e2” data arealso inserted on separate rows. In an unbounded sequence the children ofthe sequence can appear any number of times. Thus, there is no apparentrelationship between the siblings. For example, in the input XML datafile of the example shown in FIG. 5, it is impossible to determine thatthe data sets “b1,” “c1,” “d1,” and “e1” and b2,” “c2,” “d2,” and “e2”are not related to each other because they have a common parent.

Returning now to FIG. 3, the XML flattening program module 36 reviewsthe schema to determine if it contains a Choice compositor at step 335.If the schema contains a Choice compositor, the XML flattening programmodule 36 will insert the elements within this compositor on separaterows at step 340. If the schema does not contain a Choice compositor,the XML flattening program module 36 determines if the schema containsan Attribute property at step 355.

An example of an input XML schema data 38 defining elements within aChoice compositor and a conforming input XML data 37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:choice>       <xsd:element name=“B”type=“xsd:string” />       <xsd:element name=“C” type=“xsd:string” />      <xsd:element name=“D” type=“xsd:string” />      <xsd:elementname=“E” type=“xsd:string” />      </xsd:choice>     </xsd:complexType>   </xsd:element>   </xsd:sequence>  </xsd:complexType> </xsd:element>Data <root>  <A>   <E>e1</E>  </A>  <A>   <C>c2</C>  </A>  <A>  <D>d1</D>  </A>  <A>   <B>b1</B>  </A> </root>The input XML schema data 38 illustrated in the above example defineselements “B,” “C,” “D,” and “E” as choice elements within the Choicecompositor <xsd: choice>, indicating that the data contained withinthese elements do not share a relationship with each other. As a result,the XML flattener program module 36 will insert each element on aseparate row in the spreadsheet.

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the above example is shown in FIG. 6. As shown online 605 in FIG. 6, the column headings represent the elements “B,” “C,”“D,” and “E” defined within the Choice compositor in the input XMLschema data 38. On lines 610–625, the “e1,” “c2,” “d1,” and “b1” dataare inserted on separate rows.

It should be understood that the input XML schema data 38 may includecompositors nested within other compositors. For example, XML schemadata may define elements within a Choice compositor which are nestedwithin elements defined within a Sequence compositor. When flatteningnested elements, the XML flattening program module 36 inserts the datafor each element according to the layout rules for each compositor. Anexample of input XML schema data 38 defining Choice compositor elementsnested within sequence compositor elements and conforming input XML data37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:sequence>       <xsd:element name=“B”type=“xsd:string” />       <xsd:choice>        <xsd:element name=“C”type=“xsd:string” />        <xsd:element name=“D” type=“xsd:string” />      </xsd:choice>      <xsd:element name=“E” type=“xsd:string” />     </xsd:sequence>     </xsd:complexType>    </xsd:element>  </xsd:sequence>  </xsd:complexType> </xsd:element> Data <root>  <A>    <B>b1</B>     <C>c1</C>     <E>e1</E>  </A>  <A>     <B>b2</B>    <D>d1</D>     <E>e2</E>  </A> </root>The input XML schema data 38 illustrated in the above example defineselements “B” and “E” as Sequence elements indicating that the datacontained within these elements must occur in the specified order. Theinput XML schema data 38 also defines elements “C” and “D” as Choiceelements indicating that the data contained within these elements do notshare a relationship with each other. Thus, according to the XML schemadata 38. elements “B” and “E” may be in sequence with element “C” orelement “D.” The XML data 37 indicates that the elements “B,” “C,” and“E” contain the data “b1,” “e1,” and “e1” respectively. The XML data 37also indicates that the elements “B,” “D,” and “E” contain the data“b2,” “d1,” and “e2.” As discussed above, the XML flattener programmodule 36 inserts data within Sequence elements on the same row in thespreadsheet (unless maxOccurs>1) and inserts data within Choice elementson separate rows in the spreadsheet.

An illustrative resulting flat data structure 39 for the data in theconforming input XML data 37 for the above example is shown in FIG. 7.As shown on line 705 in FIG. 7, the column headings represent theelements “B,” “C,” “D,” and “E” defined within the Sequence and Choicecompositors in the input XML schema data 38. On line 710, the “b1” and“e1” Sequence data are inserted on the same row as the “c1” Choice data,while on line 715, the “b2” and “e2” Sequence data are also inserted onthe same row as the “d1” Choice data. Thus, as shown in FIG. 7, thelayout rules for both Sequence and Choice compositors are satisfied.Choice data “c1” and “d1” appear on different rows while Sequence data“b1,” “e1,” “b2,” and “e2” are in sequence with the Choice data.

Returning now to FIG. 3, the XML flattening program module 36 reviewsthe schema to determine if it defines attributes for the input XML data37 at step 345. If the schema defines attributes, the XML flatteningprogram module 36 will insert the attributes on the same row as theirparent element content at step 350. The XML flattening program module 36will also “fill down” attributes with the descendants of each parentelement (e.g., child elements) defined in the schema at step 360. If theschema does not define attributes, the XML flattening program module 36determines if the schema defines ancestor elements at step 355.

An example of input XML schema data 38 defining attributes and aconforming input XML data 37 is shown below:

Schema Data     <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:simpleContent>       <xsd:restrictionbase=“xsd:string”>    <xsd:attribute name=“ATTA” type=“xsd:integer” />  </xsd:restriction>  </xsd:simpleContent>     </xsd:complexType>   </xsd:element>   </xsd:sequence>  </xsd:complexType> </xsd:element>Data <root>  <A ATTA=“1”>A1</A>  <A ATTA=“2”>A2</A>  <A ATTA=“3”>A3</A></root>The input XML schema data 38 illustrated in the above example definesthe element “A” and the attribute “ATTA” which is further defined ashaving an integer value (specified in the input XML data as “1,” “2,” or“3.”

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the above example is shown in FIG. 8. As shown online 805 in FIG. 8, the column headings represent the attribute “ATTA”and the parent element “A” defined in the input XML schema data 38. Onlines 810–820, the integer attribute values “1,” “2,” and “3” areinserted are each inserted on the same row as parent element “A” data“A1,” “A2,” and “A3.”

Another example of input XML schema data 38 defining attributes andconforming input XML data 37 is shown below:

Schema Data     <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”>    <xsd:complexType>      <xsd:sequence>       <xsd:element name=“B”>           <xsd:complexType>          <xsd:sequence>      <xsd:elementname=“C” type=“xsd:string” />         <xsd:element name=“D”type=“xsd:string” />             </xsd:sequence>           </xsd:complexType>           </xsd:element>     </xsd:sequence>      <xsd:attribute name=“ATTA” type=“xsd:integer”/>     </xsd:complexType>    </xsd:element>   </xsd:sequence> </xsd:complexType> </xsd:element> Data  <root>   <A ATTA=“213”>     <B>       <C>C1</C>        <D>D1</D>     </B>     <B>        <C>C2</C>       <D>D2</D>     </B>   </A>  </root>The input XML schema data 38 illustrated in the above example definesthe attribute “ATTA” (having an integer value of “213”) with the parentelement “A” as well as child elements “C” and “D” within the element“B.”

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the above example is shown in FIG. 9. As shown online 905 in FIG. 9, the column headings represent the attribute “ATTA”and the repeating child elements “C” and “D.” The data for repeatingchild elements “C” and “D” are inserted on lines 910 and 915. The datavalue “213” for the attribute “ATTA” is also inserted alongside the datafor child elements “C” and “D” on lines 910 and 915. Thus, the data forthe attribute “ATTA” which does not repeat is filled down with the datafor the repeating child elements “C” and “D.”

Returning now to FIG. 3, the XML flattening program module 36 reviewsthe schema to determine if it defines ancestor elements for the inputXML data 37 at step 355. If the schema defines ancestor elements, theXML flattening program module 36 will “fill down” the ancestor elementswith their descendants at step 365. If the schema does not defineancestor elements, the XML flattening program module 36 determines ifthe schema defines sibling elements at step 370.

An example of an input XML schema data 38 defining ancestor elements anda conforming input XML data 37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>     <xsd:element name=“A” maxOccurs=“unbounded”>     <xsd:sequence>        <xsd:element name=“B” type=“xsd:string” max/>      </xsd:sequence>     </xsd:element>    <xsd:element name=“C”type=“xsd:string” />   </xsd:sequence>  </xsd:complexType></xsd:element> Data <root>  <A>   <B>B1</B>  </A>  <A>   <B>B2</B>  </A> <C>C1</C> </root>The input XML schema data 38 illustrated in the above example specifiesthat element “C” is an ancestor of element “B.” In the input XML data37, element “B” contains the data “B1” and “B2,” while the element “C”contains the content “C1.”

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the above example is shown in FIG. 10. As shown online 1005 in FIG. 10, the column headings represent the elements “B” and“C.” The data for repeating element “B” is inserted on lines 1010 and1015. The data for ancestor element “C” “fills down” along with the datafor element “B.”

Returning now to FIG. 3, the XML flattening program module 36 reviewsthe schema to determine if it defines sibling elements for the input XMLdata 37 at step 370. If the schema defines sibling elements, the XMLflattening program module 36 XML flattening program module 36 will “filldown” sibling elements with other sibling elements unless one or moresibling elements occurs more than once. If the schema does not definesibling elements, the operative flow continues to step 380 where the XMLflattening program module 36 returns to the schema to determine if thereare any more properties. If there are more properties defined in theschema, the operative flow returns to step 315 where the XML flatteningprogram module 36 determines the identity of the next property. If thereare no more properties defined in the schema, the operative flow returnsto the rendering operation 220 in FIG. 2.

An example of an input XML schema data 38 defining sibling elements anda conforming input XML data 37 is shown below:

Schema Data <xsd:element name=“root”>  <xsd:complexType>  <xsd:sequence>    <xsd:element name=“A” maxOccurs=“unbounded”/>   <xsd:element name=“B” type=“xsd:string” />   </xsd:sequence> </xsd:complexType> </xsd:element> Data <root>  <A>A1</A>  <A>A2</A> <B>B1</B> </root>The input XML schema data 38 illustrated in the above example specifiesthat elements “A” and “B” are siblings. In the input XML data 37,element “A” contains the data “A1” and “A2,” while the element “B”contains the content “B1.”

An illustrative resulting flat data structure 39 for the data in theinput XML data 37 for the above example is shown in FIG. 12. As shown online 1105 in FIG. 11, the column headings represent the elements “A” and“B.” The data for repeating element “A” is inserted on lines 1110 and1115. The data for sibling element “B” “fills down” along with the datafor element “A.”

It should be understood that when two or more sibling elements, ancestorelements, or ancestor sibling elements are defined as occurring morethan once in a schema, a Cartesian product is created. Cartesianproducts are the result of elements being combined into severalcombinations without any defined meaning. For example, if a siblingelements “A” and “B” are both defined in a schema as unbounded (i.e.,occurring more than once), any number of element “As” would have to beinserted with any number of element “Bs.” Cartesian products are oftenundesirable because they typically result in very large data sets. Forexample, given two columns of data each with “m” and “n” rows, aCartesian product would result when the “m” rows are multiplied by the“n” rows of data. This could lead to very huge datasets as the number ofrows and the number of columns increase. Furthermore, it is difficult todetermine the relationships of data in Cartesian products resulting inhuge data sets especially since the data sets might not have originallybeen related at all. As a result, the XML flattener program module 36does not “fill down” these elements.

In view of the foregoing, it will be appreciated that the presentinvention provides a method and system for converting a hierarchicaldata structure into a flat data structure based on a schema. While theinvention has been particularly shown and described with reference toillustrative embodiments thereof, it will be understood by those skilledin the art that various other changes in the form and details may bemade therein without departing from the spirit and scope of theinvention.

1. A computer-implemented method for converting a hierarchical datastructure into a flat data structure comprising: in an applicationprogram, loading hierarchical data conforming to a schema wherein thehierarchical data includes elements and attributes linked together in aparent-child relationship, wherein the schema defines hierarchicalrelationships between the elements and attributes; applying a pluralityof layout rules to the hierarchical data based on the schema to createthe flat data structure; and rendering the hierarchical data so that thehierarchical data is inserted in rows and columns in the flat datastructure, wherein applying a plurality of layout rules to thehierarchical data based on the schema comprises: identifying propertiesin the schema, wherein the properties identify occurrence requirementsfor the elements defined in the schema; and applying the plurality oflayout rules based on the identified properties, wherein the propertiesinclude a first property specifying: the order in which the elementsoccur in the hierarchical data; and the minimum and maximum number oftimes that each element may occur in the hierarchical data, wherein thefirst property is a sequence compositor or an all compositor, whereinthe hierarchical data is XML data, wherein the application program is aspreadsheet application program, wherein the flat data structure isreadily usable by an electronic spreadsheet.
 2. The method of claim 1,wherein the properties include a second property specifying the minimumand maximum number of times that chosen elements may occur in thehierarchical data.
 3. The method of claim 2, wherein the second propertyis a choice compositor.
 4. The method of claim 1, wherein the layoutrules include inserting each element on the same row within the flatdata structure according to the first property.
 5. The method of claim1, wherein the layout rules include inserting each element on a separaterow within the flat data structure according to the first property ifthe maximum number of occurrences of each element is greater than one.6. The method of claim 2, wherein the layout rules include insertingeach chosen element on a separate row within the flat data structureaccording to the second property.
 7. The method of claim 1, wherein thelayout rules include inserting each attribute within the flat datastructure.
 8. The method of claim 1, wherein the layout rules includeinserting each attribute on the same row as the content of its parentelement within the flat data structure.
 9. The method of claim 1,wherein the layout rules include inserting each attribute on the samerow as the content of its parent element within the flat data structurefor each and every occurrence of the content according.
 10. The methodof claim 1, wherein the hierarchical data includes ancestor elements andsibling elements.
 11. The method of claim 10, wherein the layout rulesinclude inserting ancestor elements on the same row as their descendantswithin the flat data structure for each and every occurrence of thedescendants.
 12. The method of claim 10, wherein the layout rulesinclude inserting sibling elements on the same row for each siblingoccurring more than once.
 13. The method of claim 10, wherein the layoutrules include inserting each sibling element on a separate row withinthe flat data structure according if the maximum number of occurrencesof a plurality of the sibling elements is greater than one.
 14. Acomputer-readable medium having computer-executable instructions forexecuting the method of claim
 1. 15. A computer system for converting ahierarchical data structure into a flat data structure comprising: amemory for storing: the hierarchical data structure, a schema associatedwith the flat data structure, and the flat data structure; and aprocessing unit functionally coupled to the memory, for executingcomputer-executable instructions operable for: loading the hierarchicaldata conforming to the schema, wherein the hierarchical data includeselements and attributes linked together in a parent-child relationship,wherein the schema defines hierarchical relationships between theelements and attributes; identifying properties in the schema, whereinthe properties identify occurrence requirements for the elements definedin the schema; applying a plurality of layout rules based on theidentified properties to create the flat data structure; and renderingthe hierarchical data so that the hierarchical data is inserted in rowsand columns in the flat data, wherein the properties include a firstproperty specifying: the order in which the elements occur in thehierarchical data; and the minimum and maximum number of times that eachelement may occur in the hierarchical data, wherein the first propertyis a sequence compositor or an all compositor, wherein the hierarchicaldata is XML data, wherein the application program is a spreadsheetapplication program, wherein the flat data structure is readily usableby an electronic spreadsheet.
 16. The system of claim 15, wherein theproperties include a second property specifying the minimum and maximumnumber of times that chosen elements may occur in the hierarchical data.17. The system of claim 16, wherein the second property is a choicecompositor.
 18. The system of claim 15, wherein the layout rules includeinserting each element on the same row within the flat data structureaccording to the first property.
 19. The system of claim 15, wherein thelayout rules include inserting each element on a separate row within theflat data structure according to the first property if the maximumnumber of occurrences of each element is greater than one.
 20. Thesystem of claim 16, wherein the layout rules include inserting eachchosen element on a separate row within the flat data structureaccording to the second property.
 21. The system of claim 15, whereinthe layout rules include inserting each element having content withinthe flat data structure.
 22. The system of claim 15, wherein the layoutrules include inserting each attribute within the flat data structure.23. The system of claim 15, wherein the layout rules include insertingeach attribute on the same row as the content of its parent elementwithin the flat data structure.
 24. The system of claim 15, wherein thelayout rules include inserting each attribute on the same row as thecontent of its parent element within the flat data structure for eachand every occurrence of the content.
 25. The system of claim 15, whereinthe layout rules include inserting ancestor elements on the same row astheir descendants within the flat data structure for each and everyoccurrence of the descendants.
 26. The system of claim 15, wherein thelayout rules include inserting sibling elements on the same row for eachsibling occurring more than once.